package com.douqu.game.mergeManager.mergeUtils;

import com.douqu.game.core.database.model.*;
import com.douqu.game.core.entity.Player;
import com.douqu.game.core.factory.LoadFactory;
import com.douqu.game.core.util.CoreUtils;
//import org.apache.ibatis.jdbc.ScriptRunner;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: Bean
 * @Description:
 * @Date: 2018-03-24 15:33
 */
public class DatabaseFactory {

    public DatabaseConfig databaseConfig;

    public DatabaseFactory(DatabaseConfig databaseConfig)
    {
        this.databaseConfig = databaseConfig;
    }

    private Connection getConnection()
    {
        try {
            // 加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection(databaseConfig.getUrl(), databaseConfig.getUser(), databaseConfig.getPwd());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<GMModel> getGMList()
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<GMModel> gmModelList = new ArrayList<>();
        try {
            connection = getConnection();
            ps = connection.prepareStatement("select * from gm");
            rs = ps.executeQuery();
            GMModel gmModel = null;
            while(rs.next())
            {
                gmModel = new GMModel();
                gmModel.setId(rs.getInt("id"));
                gmModel.setUsername(rs.getString("username"));
                gmModel.setPassword(rs.getString("password"));
                gmModel.setType(rs.getInt("type"));
                gmModel.setCreateTime(rs.getTimestamp("createTime"));
                gmModel.setDel(rs.getBoolean("isDel"));
                gmModelList.add(gmModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return gmModelList;
    }

    public List<GMLogModel> getGMLogList()
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<GMLogModel> gmLogModelList = new ArrayList<>();
        try {
            connection = getConnection();
            ps = connection.prepareStatement("select * from gm_log");
            rs = ps.executeQuery();
            GMLogModel gmLogModel = null;
            while(rs.next())
            {
                gmLogModel = new GMLogModel();
                gmLogModel.setOptionPerson(rs.getString("optionPerson"));
                gmLogModel.setContent(rs.getString("content"));
                gmLogModel.setType(rs.getInt("type"));
                gmLogModel.setValue(rs.getString("value"));
                gmLogModel.setPlayerIndex(rs.getString("playerIndex"));
                gmLogModel.setCreateTime(rs.getTimestamp("createTime"));
                gmLogModelList.add(gmLogModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return gmLogModelList;
    }

    public int getRobotCount()
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            connection = getConnection();
            ps = connection.prepareStatement("select count(1) from player where uid =0");
            rs = ps.executeQuery();
            if(rs.next())
            {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return 0;
    }
    public int getRealPlayerCount()
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            connection = getConnection();
            ps = connection.prepareStatement("select count(1) from player where uid != 0");
            rs = ps.executeQuery();
            if(rs.next())
            {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return 0;
    }

    public Player loadPlayer(String account)
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            connection = getConnection();
            ps = connection.prepareStatement("select * from player where uid = (select id from user where account=? or deviceId=?)");
            ps.setString(1, account);
            ps.setString(2, account);
            rs = ps.executeQuery();
            if(rs.next())
            {
                PlayerModel playerModel = new PlayerModel();
                playerModel.setId(rs.getInt("id"));
                playerModel.setObjectIndex(rs.getString("objectIndex"));
                playerModel.setUid(rs.getInt("uid"));
                playerModel.setName(rs.getString("name"));
                playerModel.setAvatar(rs.getString("avatar"));
                playerModel.setCamp(rs.getInt("camp"));
                playerModel.setLevel(rs.getInt("level"));
                playerModel.setExp(rs.getInt("exp"));
                playerModel.setVipLevel(rs.getInt("vipLevel"));
                playerModel.setVipExp(rs.getInt("vipExp"));
                playerModel.setMaster(rs.getInt("master"));
                playerModel.setFc(rs.getInt("fc"));
                playerModel.setMoney(rs.getInt("money"));
                playerModel.setBagInfo(rs.getBytes("bagInfo"));
                playerModel.setSettingInfo(rs.getBytes("settingInfo"));
                playerModel.setChallengeInfo(rs.getBytes("challengeInfo"));
                playerModel.setTaskInfo(rs.getBytes("taskInfo"));
                playerModel.setBoonInfo(rs.getBytes("boonInfo"));
                playerModel.setMailInfo(rs.getBytes("mailInfo"));
                playerModel.setMajorInfo(rs.getBytes("majorInfo"));
                playerModel.setIsDel(rs.getBoolean("isDel"));
                playerModel.setCreateTime(rs.getTimestamp("createTime"));
                playerModel.setLastLoginTime(rs.getTimestamp("lastLoginTime"));
                playerModel.setLastLogoutTime(rs.getTimestamp("lastLogoutTime"));

                return new Player(playerModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return null;
    }


    public List<MergePlayer> getPlayerList()
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<MergePlayer> mergePlayerList = new ArrayList<>();
        try {
            connection = getConnection();
            ps = connection.prepareStatement("select * from user");
            rs = ps.executeQuery();
            Map<Integer, UserModel> userModelMap = new HashMap<>();
            UserModel userModel = null;
            while(rs.next())
            {
                userModel = new UserModel();
                userModel.setId(rs.getInt("id"));
                userModel.setAccount(rs.getString("account"));
                userModel.setDeviceId(rs.getString("deviceId"));
                userModel.setMobile(rs.getString("mobile"));
                userModel.setPassword(rs.getString("password"));
                userModel.setIp(rs.getString("ip"));
                userModel.setChannel(rs.getInt("channel"));
                userModel.setCreateTime(rs.getTimestamp("createTime"));
                userModel.setIsDel(rs.getBoolean("isDel"));

                userModelMap.put(userModel.getId(), userModel);
            }

            ps = connection.prepareStatement("select s.id as serverId, s.name as serverName, p.* from player p, server s where p.uid != 0");
            rs = ps.executeQuery();
            PlayerModel playerModel = null;
            MergePlayer mergePlayer = null;
            while(rs.next())
            {
                playerModel = new PlayerModel();
                playerModel.setId(rs.getInt("id"));
                playerModel.setObjectIndex(rs.getString("objectIndex"));
                playerModel.setUid(rs.getInt("uid"));
                playerModel.setName(rs.getString("name"));
                playerModel.setAvatar(rs.getString("avatar"));
                playerModel.setCamp(rs.getInt("camp"));
                playerModel.setLevel(rs.getInt("level"));
                playerModel.setExp(rs.getInt("exp"));
                playerModel.setVipLevel(rs.getInt("vipLevel"));
                playerModel.setVipExp(rs.getInt("vipExp"));
                playerModel.setMaster(rs.getInt("master"));
                playerModel.setFc(rs.getInt("fc"));
                playerModel.setMoney(rs.getInt("money"));
                playerModel.setBagInfo(rs.getBytes("bagInfo"));
                playerModel.setSettingInfo(rs.getBytes("settingInfo"));
                playerModel.setChallengeInfo(rs.getBytes("challengeInfo"));
                playerModel.setTaskInfo(rs.getBytes("taskInfo"));
                playerModel.setBoonInfo(rs.getBytes("boonInfo"));
                playerModel.setMailInfo(rs.getBytes("mailInfo"));
                playerModel.setMajorInfo(rs.getBytes("majorInfo"));
                playerModel.setIsDel(rs.getBoolean("isDel"));
                playerModel.setCreateTime(rs.getTimestamp("createTime"));
                playerModel.setLastLoginTime(rs.getTimestamp("lastLoginTime"));
                playerModel.setLastLogoutTime(rs.getTimestamp("lastLogoutTime"));

                mergePlayer = new MergePlayer(userModelMap.get(playerModel.getUid()));
                mergePlayer.addPlayer(new ServerMergePlayer(rs.getInt("serverId"), rs.getString("serverName"), playerModel));
                mergePlayerList.add(mergePlayer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return mergePlayerList;
    }





    public void merge(List<MergePlayer> playerList, List<GMLogModel> gmLogModelList, List<GMModel> gmModelList) throws Exception {
        Connection connection = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        PreparedStatement ps3 = null;
        PreparedStatement ps4 = null;
        PreparedStatement ps5 = null;
        try{
            connection = getConnection();
            connection.setAutoCommit(false);

            String sql1 = "insert into user (id,account,deviceId,mobile,password,ip,channel,createTime,isDel) values (?,?,?,?,?,?,?,?,?)";
            String sql2 = "insert into player (objectIndex,uid,name,avatar,camp,level,exp,vipLevel,vipExp,master,fc,money," +
                    "bagInfo,settingInfo,challengeInfo,taskInfo,boonInfo,mailInfo,majorInfo," +
                    "isDel,createTime,lastLoginTime,lastLogoutTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            String sql3 = "insert into merge_info (playerIndex,uid,serverId,serverName,createTime) values (?,?,?,?,?)";

            String sql4 = "insert into gm_log (optionPerson,content,type,value,playerIndex,createTime) values(?,?,?,?,?,?)";

            String sql5 = "insert into gm (username,password,type,createTime,isDel) values(?,?,?,?,?)";

            ps1 = connection.prepareStatement(sql1);
            ps2 = connection.prepareStatement(sql2);
            ps3 = connection.prepareStatement(sql3);
            ps4 = connection.prepareStatement(sql4);
            ps5 = connection.prepareStatement(sql5);

            UserModel userModel = null;
            PlayerModel playerModel = null;
            Timestamp timestamp = new Timestamp(System.currentTimeMillis());
            boolean addMergeInfo = false;
            for(MergePlayer player : playerList)
            {
                userModel = player.getUserModel();
                ps1.setInt(1, player.getId());
                ps1.setString(2, userModel.getAccount());
                ps1.setString(3, userModel.getDeviceId());
                ps1.setString(4, userModel.getMobile());
                ps1.setString(5, userModel.getPassword());
                ps1.setString(6, userModel.getIp());
                ps1.setInt(7, userModel.getChannel());
                ps1.setTimestamp(8, new Timestamp(userModel.getCreateTime().getTime()));
                ps1.setBoolean(9, userModel.getIsDel());
                ps1.addBatch();

                addMergeInfo = player.getPlayerList().size() > 1;
                for(ServerMergePlayer serverMergePlayer : player.getPlayerList())
                {
                    playerModel = serverMergePlayer.getPlayerModel();
                    ps2.setString(1, playerModel.getObjectIndex());
                    ps2.setInt(2, player.getId());
                    ps2.setString(3, playerModel.getName());
                    ps2.setString(4, playerModel.getAvatar());
                    ps2.setInt(5, playerModel.getCamp());
                    ps2.setInt(6, playerModel.getLevel());
                    ps2.setInt(7, playerModel.getExp());
                    ps2.setInt(8, playerModel.getVipLevel());
                    ps2.setInt(9, playerModel.getVipExp());
                    ps2.setInt(10, playerModel.getMaster());
                    ps2.setInt(11, playerModel.getFc());
                    ps2.setInt(12, playerModel.getMoney());
                    ps2.setBytes(13, playerModel.getBagInfo());
                    ps2.setBytes(14, playerModel.getSettingInfo());
                    ps2.setBytes(15, playerModel.getChallengeInfo());
                    ps2.setBytes(16, playerModel.getTaskInfo());
                    ps2.setBytes(17, playerModel.getBoonInfo());
                    ps2.setBytes(18, playerModel.getMailInfo());
                    ps2.setBytes(19, playerModel.getMajorInfo());
                    ps2.setBoolean(20, playerModel.getIsDel());
                    ps2.setTimestamp(21, new Timestamp(playerModel.getCreateTime().getTime()));
                    ps2.setTimestamp(22, new Timestamp(playerModel.getLastLoginTime().getTime()));
                    if(playerModel.getLastLogoutTime() != null)
                        ps2.setTimestamp(23, new Timestamp(playerModel.getLastLogoutTime().getTime()));
                    else
                        ps2.setObject(23, null);
                    ps2.addBatch();

                    if(addMergeInfo)
                    {
                        ps3.setString(1, playerModel.getObjectIndex());
                        ps3.setInt(2, player.getId());
                        ps3.setInt(3, serverMergePlayer.getServerId());
                        ps3.setString(4, serverMergePlayer.getServerName());
                        ps3.setTimestamp(5, timestamp);
                        ps3.addBatch();
                    }
                }
            }

            for(GMLogModel gmLogModel : gmLogModelList)
            {
                ps4.setString(1, gmLogModel.getOptionPerson());
                ps4.setString(2, gmLogModel.getContent());
                ps4.setInt(3, gmLogModel.getType());
                ps4.setString(4, gmLogModel.getValue());
                ps4.setString(5, gmLogModel.getPlayerIndex());
                ps4.setTimestamp(6, new Timestamp(gmLogModel.getCreateTime().getTime()));
                ps4.addBatch();
            }

            for(GMModel gmModel : gmModelList)
            {
                ps5.setString(1, gmModel.getUsername());
                ps5.setString(2, gmModel.getPassword());
                ps5.setInt(3, gmModel.getType());
                ps5.setTimestamp(4, new Timestamp(gmModel.getCreateTime().getTime()));
                ps5.setBoolean(5, gmModel.isDel());
                ps5.addBatch();
            }

            ps1.executeBatch();
            ps2.executeBatch();
            ps3.executeBatch();
            ps4.executeBatch();
            ps5.executeBatch();

            connection.commit();
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception(e);
        }finally {
            if(ps1 != null)
                try {
                    ps1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(ps2 != null)
                try {
                    ps2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            if(ps3 != null)
                try {
                    ps3.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }



    public List<Player> getAllPlayer()
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Player> playerList = new ArrayList<>();
        try {
            connection = getConnection();
            ps = connection.prepareStatement("select * from player");
            rs = ps.executeQuery();
            PlayerModel playerModel = null;
            while(rs.next())
            {
                playerModel = new PlayerModel();
                playerModel.setId(rs.getInt("id"));
                playerModel.setObjectIndex(rs.getString("objectIndex"));
                playerModel.setUid(rs.getInt("uid"));
                playerModel.setName(rs.getString("name"));
                playerModel.setAvatar(rs.getString("avatar"));
                playerModel.setCamp(rs.getInt("camp"));
                playerModel.setLevel(rs.getInt("level"));
                playerModel.setExp(rs.getInt("exp"));
                playerModel.setVipLevel(rs.getInt("vipLevel"));
                playerModel.setVipExp(rs.getInt("vipExp"));
                playerModel.setMaster(rs.getInt("master"));
                playerModel.setFc(rs.getInt("fc"));
                playerModel.setMoney(rs.getInt("money"));
                playerModel.setBagInfo(rs.getBytes("bagInfo"));
                playerModel.setSettingInfo(rs.getBytes("settingInfo"));
                playerModel.setChallengeInfo(rs.getBytes("challengeInfo"));
                playerModel.setTaskInfo(rs.getBytes("taskInfo"));
                playerModel.setBoonInfo(rs.getBytes("boonInfo"));
                playerModel.setMailInfo(rs.getBytes("mailInfo"));
                playerModel.setMajorInfo(rs.getBytes("majorInfo"));
                playerModel.setIsDel(rs.getBoolean("isDel"));
                playerModel.setCreateTime(rs.getTimestamp("createTime"));
                playerModel.setLastLoginTime(rs.getTimestamp("lastLoginTime"));
                playerModel.setLastLogoutTime(rs.getTimestamp("lastLogoutTime"));

                playerList.add(new Player(playerModel));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return playerList;
    }

    public ServerModel getServerModel(){
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ServerModel serverModel = null;
        try{
            connection = getConnection();
            ps = connection.prepareStatement("SELECT * FROM server LIMIT 1");
            rs = ps.executeQuery();
            while(rs.next())
            {
                serverModel = new ServerModel();
                serverModel.setName(rs.getString("name"));
                serverModel.setPlayerIndex(rs.getInt("playerIndex"));
                serverModel.setGoodsIndex(rs.getInt("goodsIndex"));
                serverModel.setAuthServerHost(rs.getString("authServerHost"));
                serverModel.setFreeCount(rs.getInt("freeCount"));
                serverModel.setCrowdCount(rs.getInt("crowdCount"));
                serverModel.setWorldInfo(rs.getBytes("worldInfo"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return serverModel;
    }

    public List<Map<String, Object>> query(String sql)
    {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            connection = getConnection();
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            Map<String, Object> result = null;
            ResultSetMetaData metaData = rs.getMetaData();
            int count = metaData.getColumnCount();
            while(rs.next())
            {
                result = new HashMap<>();
                for(int i = 1; i <= count; i++)
                {
                    result.put(metaData.getColumnLabel(i), rs.getObject(i));
                }
                list.add(result);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return list;
    }


    public int execute(String sql)
    {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = getConnection();
            ps = connection.prepareStatement(sql);
            ps.execute();
            return ps.getUpdateCount();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return 0;
    }


    public boolean executeSqlFile(String sqlFileName)
    {
        Connection connection = null;
        ScriptRunner runner = null;
        try{
            connection = getConnection();
            connection.setAutoCommit(false);
            runner = new ScriptRunner(connection);
            PrintWriter printWriter = new PrintWriter(System.out);
            runner.setErrorLogWriter(printWriter);
            runner.setLogWriter(printWriter);
            // 执行SQL脚本
            runner.runScript(new InputStreamReader(getFileInputStream(sqlFileName)));
            connection.setAutoCommit(true);
            return true;
        }catch (Exception e){
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
            return false;
        }finally {
            if(runner != null)
                runner.closeConnection();
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public boolean isTableExist(String tableName)
    {
        Connection connection = getConnection();
        ResultSet rsTables = null;
        try {
            rsTables = connection.getMetaData().getTables(null, null, tableName, null);
            return rsTables.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                rsTables.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public boolean isDBExist()
    {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DriverManager.getConnection(databaseConfig.getUrl(), databaseConfig.getUser(), databaseConfig.getPwd());
            return connection != null;
        } catch (Exception e) {
            return false;
        }finally {

            if(ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            if(connection != null)
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }


    public static InputStream getFileInputStream(String filePath) throws Exception
    {
        File file = null;
        if(filePath.startsWith("C:") || filePath.startsWith("D:")
                || filePath.startsWith("E:") || filePath.startsWith("F:") || filePath.startsWith("G:"))
            file = new File(filePath);
        else
            file = new File(CoreUtils.getBasePath() + filePath);

        if(!file.exists())
            file = new File(CoreUtils.getRootPath() + filePath);

        InputStream input = null;
        if(file.exists())
        {
            input = new BufferedInputStream(new FileInputStream(file));
        }
        else
        {
            input = LoadFactory.class.getClassLoader().getResourceAsStream(filePath);
        }
        return input;
    }
}
